﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class CustomerRevisitDAL
    {
        private const string SQL_SELECT_WS_CUSTOMER_REVISIT = "select CRV_CUSTOMER_ID as CUSTOMER_ID,CRV_REVISIT_ID AS REVISIT_ID,date_format(CRV_REVISIT_TIME,'%Y-%m-%d %H:%i:%s') as REVISIT_TIME,CRV_STAFF_ID as STAFF_ID,CRV_REVISIT_REMARK as REVISIT_REMARK,CRV_RCRE_STAFF_ID as RCRE_STAFF_ID,CRV_RCRE_TIME as RCRE_TIME,CRV_LCHG_STAFF_ID as LCHG_STAFF_ID,CRV_LCHG_TIME as LCHG_TIME,(select STF_STAFF_NAME from WS_STAFF where STF_STAFF_ID=CRV_STAFF_ID) as STAFF_NAME from WS_CUSTOMER_REVISIT where 1=1";
        private const string SQL_INSERT_WS_CUSTOMER_REVISIT = "insert into WS_CUSTOMER_REVISIT(CRV_CUSTOMER_ID,CRV_REVISIT_ID,CRV_REVISIT_TIME,CRV_STAFF_ID,CRV_REVISIT_REMARK,CRV_RCRE_STAFF_ID,CRV_RCRE_TIME) values(@CustomerId,@RevisitId,@RevisitTime,@StaffId,@RevisitRemark,@LastStaffId,@LastTime)";
        private const string SQL_UPDATE_WS_CUSTOMER_REVISIT = "update WS_CUSTOMER_REVISIT set CRV_REVISIT_TIME=@RevisitTime,CRV_STAFF_ID=@StaffId,CRV_REVISIT_REMARK=@RevisitRemark,CRV_LCHG_STAFF_ID=@LastStaffId,CRV_LCHG_TIME=@LastTime where 1=1";
        private const string SQL_DELETE_WS_CUSTOMER_REVISIT = "delete from WS_CUSTOMER_REVISIT where 1=1";
        private const string SQL_COUNT_WS_CUSTOMER_REVISIT = "select count(CRV_REVISIT_ID) from WS_CUSTOMER_REVISIT where 1=1";

        private const string SQL_WHERE_CUSTOMER_ID = " and CRV_CUSTOMER_ID=@CustomerId ";
        private const string SQL_WHERE_REVISIT_ID = " and CRV_REVISIT_ID=@RevisitId ";

        private const string PARM_CUSTOMER_ID = "@CustomerId";
        private const string PARM_REVISIT_ID = "@RevisitId";
        private const string PARM_REVISIT_TIME = "@RevisitTime";
        private const string PARM_STAFF_ID = "@StaffId";
        private const string PARM_REVISIT_REMARK = "@RevisitRemark";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        Nullable<DateTime> NullDateTime = null;
        private static CustomerRevisitDAL instance = new CustomerRevisitDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static CustomerRevisitDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(string customerId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_CUSTOMER_REVISIT);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = customerId });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Insert(DbTransaction trans, CustomerRevisitModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_REVISIT_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RevisitId });
            parms.Add(new MySqlParameter(PARM_REVISIT_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.RevisitTime });
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
            parms.Add(new MySqlParameter(PARM_REVISIT_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RevisitRemark });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_CUSTOMER_REVISIT, parms.ToArray());
        }
        public int Update(DbTransaction trans, CustomerRevisitModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_CUSTOMER_REVISIT);
            sbSQL.Append(SQL_WHERE_REVISIT_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_REVISIT_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RevisitId });
            parms.Add(new MySqlParameter(PARM_REVISIT_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.RevisitTime });
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
            parms.Add(new MySqlParameter(PARM_REVISIT_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RevisitRemark });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_CUSTOMER_REVISIT);
            sbSQL.Append(SQL_WHERE_REVISIT_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_REVISIT_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_CUSTOMER_REVISIT);
            sbSQL.Append(SQL_WHERE_REVISIT_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_REVISIT_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
